1. Field of the Invention
The present invention generally relates to storage systems and more particularly to an improved system that allocates an extensible tablespace based on a policy and manages a storage system based on the policy.
2. Description of the Related Art
In current systems, significant expertise is required by both database administrators (DBA) and storage administrators (SA) to manage storage for databases. They spend a significant amount of time together in managing storage for databases. Some of the problems they face are, initial configuration, system growth, and dynamic workload variations.
With respect to initial configuration, when a database application needs to be brought online, the DBA and SA have to go through a tedious process for laying out tables onto the storage. They have to inventory all the storage devices, determine their performance characteristics, determine the space available on all of them, estimate the space requirement for each of the tables, and then map all the tables onto the various devices. This process is currently manual and very error prone.
With respect to system growth, the storage requirements for database applications usually grow with time. If the DBA and SA incorrectly estimate the total amount of future space required for the numerous tables, then the database will run out of storage. This causes the application to fail resulting in application downtime. Resolution of this problem requires intervention by the database and storage administrators to allocate more storage and make it available to the appropriate table.
With respect to dynamic workload variations, the database workload varies over time. For example, the workload mix between Online Transaction Processing (OPT) and Decision Support System (DSS) may change. Furthermore, the query execution patterns may vary. The variations can create hot spots in the storage system. For example, initially a particular disk may receive very little requests. However, due to the change in query mix, that disk may start receiving a significant number of input/output (I/O) requests. Since the query mix and/or execution pattern itself changes over time, the disk that is “hot” (e.g., that is being used excessively when compared to normal usage patterns, or when compared to the other disks in the system) also keeps varying over time. It is difficult for the administrators, that are busy just keeping the system running, to continuously optimize the storage for the dynamically changing workload. This frequently results in sub-optimal storage performance.
These storage management problems result in a high cost of management for databases. FIG. 1 depicts that current situation in storage management for databases. More specifically, as shown in FIG. 1, the DBA 120 manually configures storage for the database 121, manages the database 121 to keep running out of space, and continually tunes the storage in the database 121. Similarly, the SA 122 manually allocates, configures, and tunes the different parts of the storage 124. Arrow 123 indicates the extensive management time and resources consumed between the DBA and SA in the negotiations and communication needed for resolution of the various storage needs.
Some tools have been developed by various software vendors to reduce the storage management costs for databases. For system growth, there are two different solutions for this problem. The first is Resumable Space Allocation from Oracle (Sunnyvale, Calif., USA). In this solution, a transaction/session can be marked resumable, if such a transaction runs out of space, it will be suspended and another transaction will be executed. The triggered transaction will usually log the error and may send an email/pager to the DBA. The DBA on notification can then add more storage for the appropriate table and resume the transaction. Though this solution stops an application from failing, it has very limited applicability. For example, consider an (on-line transaction processing) OLTP transaction that runs out of space. This solution will keep the transaction hanging until the DBA allocates space.
Another solution monitors storage space utilization (Quest/Precise, Mountain View Calif., USA) monitors the storage utilization of various tables and predicts when the tables will run out of space. When the storage utilization reaches certain threshold, it triggers the execution of a script. This script may send an email/page to the DBA or add some more storage from a predefined pool. This approach works to a limited extent as it cannot handle out of space error conditions during loading of tables or when the update activity has a surge. Furthermore, this solution is ad hoc as storage management functions such as inventory of free space and the policies for storage allocation all need to be encoded into it.
With respect to dynamic workload variations a software program Database Tuner from EMC (Sunnyvale Calif. USA) correlates storage performance with database level objects. Thus, such a program facilitates the determination of storage bottlenecks in the database; however, it does not continuously tune the storage system for optimal performance.
As described in detail below, this invention presents a method and a system for significantly reducing these management costs.